﻿<%@ WebHandler Language="C#" Class="SIPO.Web.AjaxHandler.Administration.recImpositionUploadhandler" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

using SIPO.Library.DataAccess.Administration;
using SIPO.Library.DomainObject.Administration;
using SIPO.Library.DataAccess.Transaction;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DataAccess.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DataAccess.Administration.UserManagement;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.Enum;
using SIPO.Web.Support;
using Newtonsoft.Json.Linq;
using System.IO;
using Excel;
using System.Data;
using System.Text;
using System.Reflection;
using System.Web.SessionState;

namespace SIPO.Web.AjaxHandler.Administration
{

    public class recImpositionUploadhandler : BaseHandler, IRequiresSessionState
    {

        private recLogRepo logDal = new recLogRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recDebtorRepo debtorDal = new recDebtorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recDebtorSectorRepo recDebtorSectorDal = new recDebtorSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recImpositionRepo impositionDal = new recImpositionRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recSectorRepo recSectorDal = new recSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recSubSectorRepo recSubSectorDal = new recSubSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recSubSubSectorRepo recSubSubSectorDal = new recSubSubSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recTransactionTypeRepo transTypeDal = new recTransactionTypeRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recTariffRepo tariffDal = new recTariffRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);

        private string MethodName = string.Empty;
        #region Properties
        private StringBuilder ErrorMessage { get; set; }
        private bool HasError = false;
        #endregion Properties
        #region jQGrid Object Class

        public class jQGrid
        {
            public string SortName
            {
                get;
                set;
            }

            public List<colModel> ColModel
            {
                get;
                set;
            }

        }

        public class colModel
        {
            public string name
            {
                get;
                set;
            }

            public string index
            {
                get;
                set;
            }
        }

        #endregion

        public override void ProcessRequest(HttpContext context)
        {
            base.ProcessRequest(context);

            MethodName = context.Request.Params["method"];

            context.Response.Clear();
            context.Response.Buffer = true;

            switch (MethodName.ToLower())
            {
                case ("getdataexcel"):
                    context.Response.Write(GetData(context));
                    break;
                case ("delete"):
                    System.IO.StreamReader sr = new System.IO.StreamReader(context.Request.InputStream);
                    string jsonString = string.Empty;
                    jsonString = context.Server.UrlDecode(sr.ReadToEnd());
                    JObject jObj = string.IsNullOrEmpty(jsonString) ? null : JObject.Parse(jsonString);
                    sr.Close();
                    sr.Dispose();
                    context.Response.Write(Delete(context, jObj));
                    break;
                case ("checkexcel"):
                    context.Response.Write(CheckExcel(context));
                    break;
                case ("insert"):
                    context.Response.Write(Insert(context));
                    break;
                case ("dowloadtemplate"):
                    context.Response.Write(DownloadTemplate(context));
                    break;
                default:
                    // do nothing
                    break;
            }

            context.Response.Flush();
            context.Response.End();

        }

        public override bool IsReusable
        {
            get
            {
                return false;
            }
        }

        private string Delete(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                var strId = (string)jo["LogID"];

                string[] ids = strId.Split(',');
                foreach (string id in ids)
                {
                    int intID = Convert.ToInt32(id);
                    logDal.Delete(intID);
                }

                helper.IsSuccess = true;
                helper.Message = "Data berhasil di hapus !";
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Delete";
                log.LogMessage = helper.Message;
                log.LogSource = "sLoghandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);

            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string DownloadTemplate(HttpContext context)
        {
            try
            {
                var table = impositionDal.DownloadTemplate();
                List<string> timeColumns = new List<string>();

                for (int i = 0; i < table.Columns.Count; i++)
                {
                    string columnName = table.Columns[i].ColumnName;
                    if (table.Columns[i].DataType == typeof(DateTime))
                    {
                        timeColumns.Add(columnName);
                    }
                }
                timeColumns.ForEach(delegate(string col)
                {
                    table.Columns.Remove(col);
                    table.Columns.Add(col, typeof(string));
                });
                if (table.Columns.Count > 0)
                    this.ExportToExcel(context, table, "template");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "DownloadTemplate";
                log.LogMessage = "download berhasil";
                log.LogSource = "recImpositionUploadhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);

            }
            return "";
        }

        private string CheckExcel(HttpContext context)
        {
            JSONHelper helper = new JSONHelper();
            int page = 1;
            int pageSize = 10;
            string sidx = string.Empty, sord = string.Empty;

            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);
            sord = (string.IsNullOrEmpty(context.Request.QueryString["sord"]) ? "ASC" : context.Request.QueryString["sord"].ToString());

            bool IsSuccess = true;
            List<recImpositionUploadform> coll_Upload = null;
            this.ErrorMessage = new StringBuilder();
            if (context.Request.Files.Count > 0)
            {
                DataTable dt;
                HttpPostedFile hpf = context.Request.Files[0] as HttpPostedFile;
                string FileName = string.Empty;
                if (HttpContext.Current.Request.Browser.Browser.ToUpper() == "IE")
                {
                    string[] files = hpf.FileName.Split(new char[] { '\\' });
                    FileName = files[files.Length - 1];
                }
                else
                {
                    FileName = hpf.FileName;
                }

                byte[] datas = new byte[hpf.ContentLength];
                hpf.InputStream.Position = 0;
                hpf.InputStream.Read(datas, 0, hpf.ContentLength);

                if (FileName.ToLower().Contains(".xls"))
                {
                    Stream s = hpf.InputStream;
                    s.Read(datas, 0, hpf.ContentLength);
                    this.ConvertDataToDataTable(out dt, s);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        //this.ParseValueToDTO(dt.Rows, collM_AssetDTO, collM_Asset_BookDTO);
                        coll_Upload = this.ParseDataToVariable(dt.Rows);
                        dt.Dispose();
                    }
                }
                hpf.InputStream.Close();
                hpf.InputStream.Dispose();
            }

            Dictionary<int, Dictionary<int, string>> dictError = new Dictionary<int, Dictionary<int, string>>();
            //bool IsNewSession = false;

            jQGrid grid = this.BuildGrid(typeof(recImpositionUploadform));
            sidx = (string.IsNullOrEmpty(context.Request.QueryString["sidx"]) ? grid.SortName : context.Request.QueryString["sidx"].ToString());
            coll_Upload = this.OrderList(coll_Upload, sidx, sord);

            //bool HasError = this.ValidateIt(collAsset_Upload, dictError);//, IsNewSession);
            //IsSuccess = !HasError;
            int totalCount = coll_Upload.Count;
            decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
            int totalPages = Convert.ToInt32(dc);

            int startItemNumber = (page - 1) * pageSize;
            int endItemNumber = startItemNumber + (pageSize);
            List<recImpositionUploadform> pagedData = coll_Upload.Take(endItemNumber).Skip(startItemNumber).ToList();

            if (IsSuccess)
            {
                context.Session.Add("DataUpload", coll_Upload);
                helper.IsSuccess = true;
                helper.Message = "Data valid !";
                this.GetData(context);
            }
            return "";
        }
        private void ConvertDataToDataTable(out DataTable dt, Stream stream)
        {
            dt = null;

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            //DataSet result = excelReader.AsDataSet();
            //...
            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            //5. Data Reader methods
            //while (excelReader.Read())
            //{
            //    excelReader.GetInt32(0);
            //}
            dt = result.Tables.Count < 1 ? null : result.Tables[0];
            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
        }
        private List<recImpositionUploadform> ParseDataToVariable(DataRowCollection dataRowCollection)
        {
            int debtorId = 0;
            int debtorSectorId = 0;
            int sectorId = 0;
            int subsectorId = 0;
            int subsubsectorId = 0;
            List<recImpositionUploadform> coll_Upload = new List<recImpositionUploadform>();
            foreach (DataRow dataRow in dataRowCollection)
            {
                recImpositionUploadform recImpositionUpload = new recImpositionUploadform();
                recImpositionUpload.DebtorCode = dataRow["Kode Debitur"] == DBNull.Value ? string.Empty : dataRow["Kode Debitur"].ToString();
                recImpositionUpload.DebtorName = dataRow["Nama Debitur"] == DBNull.Value ? string.Empty : dataRow["Nama Debitur"].ToString();
                recImpositionUpload.SectorCode = dataRow["Kode Sektor"] == DBNull.Value ? string.Empty : dataRow["Kode Sektor"].ToString();
                recImpositionUpload.SectorName = dataRow["Nama Sektor"] == DBNull.Value ? string.Empty : dataRow["Nama Sektor"].ToString();
                recImpositionUpload.SubSectorCode = dataRow["Kode Sub Sektor"] == DBNull.Value ? string.Empty : dataRow["Kode Sub Sektor"].ToString();
                recImpositionUpload.SubSectorName = dataRow["Nama Sub Sektor"] == DBNull.Value ? string.Empty : dataRow["Nama Sub Sektor"].ToString();
                recImpositionUpload.SubSubSectorCode = dataRow["Kode Sub Sub Sektor"] == DBNull.Value ? string.Empty : dataRow["Kode Sub Sub Sektor"].ToString();
                recImpositionUpload.SubSubSectorName = dataRow["Nama Sub Sub Sektor"] == DBNull.Value ? string.Empty : dataRow["Nama Sub Sub Sektor"].ToString();
                recImpositionUpload.YearReport = dataRow["Tahun Laporan Keuangan"] == DBNull.Value ? string.Empty : dataRow["Tahun Laporan Keuangan"].ToString();
                recImpositionUpload.OJKValue = dataRow["Nilai Pengenaan OJK"] == DBNull.Value ? string.Empty : dataRow["Nilai Pengenaan OJK"].ToString();
                recDebtor debtor = new recDebtor();
                recSector sector = new recSector();
                recSubSector subsector = new recSubSector();
                recSubSubSector subsubsector = new recSubSubSector();
                if (!string.IsNullOrEmpty(recImpositionUpload.DebtorCode))
                    debtor = debtorDal.GetByDebtorCode(recImpositionUpload.DebtorCode);
                if (debtor != null)
                    debtorId = debtor.DebtorID;
                if (!string.IsNullOrEmpty(recImpositionUpload.SectorCode))
                    sector = recSectorDal.GetBySectorCode(int.Parse(recImpositionUpload.SectorCode));
                if (sector != null)
                    sectorId = sector.SectorID;
                if (!string.IsNullOrEmpty(recImpositionUpload.SubSectorCode))
                    subsector = recSubSectorDal.GetBySubSectorCode(int.Parse(recImpositionUpload.SubSectorCode), sectorId);
                if (subsector != null)
                    subsectorId = subsector.SubSectorID;
                if (!string.IsNullOrEmpty(recImpositionUpload.SubSubSectorCode))
                    subsubsector = recSubSubSectorDal.GetBySubSubSectorCode(int.Parse(recImpositionUpload.SubSubSectorCode), subsectorId);
                if (subsubsector != null)
                    subsubsectorId = subsubsector.SubSubSectorID;
                recDebtorSector debtorsector = recDebtorSectorDal.GetById(debtorId, subsubsectorId);
                if (debtorsector != null)
                    debtorSectorId = debtorsector.DebtorSectorID;
                recImposition recImposition = impositionDal.GetByYearReport(recImpositionUpload.YearReport, debtorSectorId, subsubsectorId);
                if (recImposition != null)
                    recImpositionUpload.DebtorValue = recImposition.DebtorValue.ToString();
                else
                    recImpositionUpload.DebtorValue = "0";
                if (!string.IsNullOrEmpty(recImpositionUpload.DebtorCode) && !string.IsNullOrEmpty(recImpositionUpload.DebtorName) && !string.IsNullOrEmpty(recImpositionUpload.SectorCode) &&
                    !string.IsNullOrEmpty(recImpositionUpload.SectorName) && !string.IsNullOrEmpty(recImpositionUpload.SubSectorCode) && !string.IsNullOrEmpty(recImpositionUpload.SubSectorName) &&
                    !string.IsNullOrEmpty(recImpositionUpload.SubSubSectorCode) && !string.IsNullOrEmpty(recImpositionUpload.SubSubSectorName) && !string.IsNullOrEmpty(recImpositionUpload.YearReport) &&
                        !string.IsNullOrEmpty(recImpositionUpload.OJKValue))
                    coll_Upload.Add(recImpositionUpload);
            }
            return coll_Upload;
        }
        public List<T> OrderList<T>(IList<T> collection, string OrderBy, string SortMode)
        {
            string property = OrderBy;
            PropertyInfo prop = typeof(T).GetProperty(property);
            if (prop == null)
                throw new Exception("No property '" + property + "' in + " + typeof(T).Name + "'");

            if (SortMode.ToLower() == "desc")
                return collection.OrderByDescending(x => prop.GetValue(x, null)).ToList();
            else
                return collection.OrderBy(x => prop.GetValue(x, null)).ToList();
        }

        private object ViewData(bool isSuccess, string errorMessage, int page, int pageSize, jQGrid grid, int totalPages, int totalCount, object pagedData, Dictionary<int, Dictionary<int, string>> dictError)
        {
            // send params into jqGrid's format
            var result = new
            {
                isSuccess = isSuccess,
                errorMessage = errorMessage,
                grid = grid,
                page = page,
                pagesize = pageSize,
                total = totalPages,
                records = totalCount,
                rows = pagedData,
                dictError = dictError
            };

            return result;

        }
        private jQGrid BuildGrid(Type type)
        {
            jQGrid grid = new jQGrid();
            grid.ColModel = new List<colModel>();
            PropertyInfo[] infoProperties = type.GetProperties();
            for (int i = 0; i < infoProperties.Count(); i++)
            {
                PropertyInfo pi = infoProperties[i];
                if (i == 0)
                {
                    grid.SortName = pi.Name;
                }
                colModel colModel = new colModel();
                colModel.index = pi.Name;
                colModel.name = pi.Name;//.Replace("_", " ");
                grid.ColModel.Add(colModel);
            }
            return grid;
        }
        private string GetData(HttpContext context)
        {
            JSONHelper helper = new JSONHelper();
            int page = 1;
            int pageSize = 10;
            string sidx = string.Empty, sord = string.Empty;

            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);
            sord = (string.IsNullOrEmpty(context.Request.QueryString["sord"]) ? "ASC" : context.Request.QueryString["sord"].ToString());

            bool IsSuccess = true;
            this.ErrorMessage = new StringBuilder();
            List<recImpositionUploadform> coll_Upload = HttpContext.Current.Session["DataUpload"] as List<recImpositionUploadform>;
            Dictionary<int, Dictionary<int, string>> dictError = new Dictionary<int, Dictionary<int, string>>();
            //bool IsNewSession = false;

            jQGrid grid = this.BuildGrid(typeof(recImpositionUploadform));
            sidx = (string.IsNullOrEmpty(context.Request.QueryString["sidx"]) ? grid.SortName : context.Request.QueryString["sidx"].ToString());
            coll_Upload = this.OrderList(coll_Upload, sidx, sord);

            coll_Upload = this.ValidateIt(coll_Upload, dictError);//, IsNewSession);
            IsSuccess = !HasError;
            int totalCount = coll_Upload.Count;
            decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
            int totalPages = Convert.ToInt32(dc);

            int startItemNumber = (page - 1) * pageSize;
            int endItemNumber = startItemNumber + (pageSize);
            List<recImpositionUploadform> pagedData = coll_Upload.Take(endItemNumber).Skip(startItemNumber).ToList();

            if (IsSuccess)
            {
                context.Session.Add("DataUpload", coll_Upload);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(ViewData(IsSuccess, this.ErrorMessage.ToString(), page, pageSize, grid, totalPages, totalCount, pagedData, dictError));
        }

        private string Insert(HttpContext context)
        {
            JSONHelper helper = new JSONHelper();
            try
            {
                var strdebtorcode = context.Request.Params["debtorcode"];
                string[] debtorcodes = strdebtorcode.Split(',');

                //recTransactionType transType = transTypeDal.GetByLikeTransTypeName("iuran berkala");
                recTransactionType transType = transTypeDal.GetByTransTypeCode(2);
                List<recImpositionUploadform> coll_Upload = HttpContext.Current.Session["DataUpload"] as List<recImpositionUploadform>;
                List<recImposition> collImpositions = new List<recImposition>();
                List<recImpositionDetail> collImpositionDetails = new List<recImpositionDetail>();
                bool IsSuccess = false;
                int debtorId = 0;
                int subSubSectorId = 0;
                int sectorId = 0;
                int subsectorId = 0;
                int debtorSectorId = 0;
                bool isFormula = false;
                decimal? value = 0;
                decimal? minvalue = 0;
                decimal? maxvalue = 0;
                decimal? percentage = 0;
                int tarifId = 0;
                int impositionBaseId = 0;
                int impositionId = 0;
                decimal debtorvalue = 0;
                if (coll_Upload != null && coll_Upload.Count > 0)
                {
                    if (!string.IsNullOrEmpty(strdebtorcode))
                    {
                        for (int i = 0; i < coll_Upload.Count; i++)
                        {
                            recImpositionUploadform Data_Upload = coll_Upload[i];
                            foreach (string debtorcode in debtorcodes)
                            {
                                if (Data_Upload.DebtorCode.Trim() == debtorcode)
                                {
                                    recDebtor debtor = debtorDal.GetByDebtorCode(Data_Upload.DebtorCode);
                                    recSector sector = recSectorDal.GetBySectorCode(int.Parse(Data_Upload.SectorCode));
                                    if (sector != null)
                                        sectorId = sector.SectorID;
                                    recSubSector subsector = recSubSectorDal.GetBySubSectorCode(int.Parse(Data_Upload.SubSectorCode), sectorId);
                                    if (subsector != null)
                                        subsectorId = subsector.SubSectorID;
                                    recSubSubSector subsubsector = recSubSubSectorDal.GetBySubSubSectorCode(int.Parse(Data_Upload.SubSubSectorCode), subsectorId);
                                    if (subsubsector != null)
                                        subSubSectorId = subsubsector.SubSubSectorID;
                                    if (debtor != null)
                                        debtorId = debtor.DebtorID;
                                    recTariff tariff = tariffDal.GetBySubSubSectorId((short)subSubSectorId, transType.TransTypeID);
                                    if (tariff != null)
                                    {
                                        isFormula = tariff.IsFormula;
                                        value = tariff.Value == null ? 0 : tariff.Value;
                                        minvalue = tariff.MinValue == null ? 0 : tariff.MinValue;
                                        maxvalue = tariff.MaxValue == null ? 0 : tariff.MaxValue;
                                        percentage = tariff.Percentage == null ? 0 : tariff.Percentage;
                                        tarifId = tariff.TariffID;
                                        impositionBaseId = tariff.ImpositionBaseID == null ? 0 : (int)tariff.ImpositionBaseID;
                                    }
                                    recDebtorSector debtorsector = recDebtorSectorDal.GetById(debtorId, subSubSectorId);
                                    if (debtorsector != null)
                                        debtorSectorId = debtorsector.DebtorSectorID;
                                    recImposition objgetImpositionId = impositionDal.GetByYearReport(Data_Upload.YearReport, debtorSectorId, subSubSectorId);
                                    if (objgetImpositionId != null)
                                    {
                                        impositionId = objgetImpositionId.ImpositionID;
                                        debtorvalue = (decimal)objgetImpositionId.DebtorValue;
                                    }
                                    recImposition obj = new recImposition();
                                    obj.ImpositionID = impositionId;
                                    obj.YearReport = short.Parse(Data_Upload.YearReport);
                                    obj.DebtorSectorID = debtorSectorId;
                                    obj.DebtorValue = debtorvalue;
                                    if (Data_Upload.OJKValue != "")
                                        obj.OJKValue = decimal.Parse(Data_Upload.OJKValue);
                                    obj.ImpositionBaseID = impositionBaseId;
                                    obj.Maximal = maxvalue;
                                    obj.Minimal = minvalue;
                                    obj.DebtorID = debtorId;
                                    obj.SubSubSectorID = subSubSectorId;
                                    obj.TariffValue = value;
                                    obj.TariffID = tarifId;
                                    obj.Percentage = percentage;
                                    obj.IsFormula = isFormula;
                                    obj.TransTypeID = transType.TransTypeID;
                                    obj.CreatedDate = DateTime.Now;
                                    obj.CreatedBy = base.UserLoggedIn;

                                    recImpositionDetail objDetail = new recImpositionDetail();
                                    objDetail.DebtorValue = debtorvalue;
                                    if (Data_Upload.OJKValue != "")
                                        objDetail.OJKValue = decimal.Parse(Data_Upload.OJKValue);
                                    if (base.UserTypeID == 2)
                                        objDetail.OJKValue = 0;
                                    else if (base.UserTypeID == 3)
                                        objDetail.DebtorValue = 0;
                                    objDetail.IsActive = true;
                                    objDetail.TransDate = DateTime.Now;
                                    objDetail.CreateDate = DateTime.Now;
                                    objDetail.CreateBy = base.UserLoggedIn;
                                    collImpositions.Add(obj);
                                    collImpositionDetails.Add(objDetail);
                                }
                            }
                        }
                    }
                    else
                    {
                        for (int i = 0; i < coll_Upload.Count; i++)
                        {
                            recImpositionUploadform Data_Upload = coll_Upload[i];
                            recDebtor debtor = debtorDal.GetByDebtorCode(Data_Upload.DebtorCode);
                            recSector sector = recSectorDal.GetBySectorCode(int.Parse(Data_Upload.SectorCode));
                            if (sector != null)
                                sectorId = sector.SectorID;
                            recSubSector subsector = recSubSectorDal.GetBySubSectorCode(int.Parse(Data_Upload.SubSectorCode), sectorId);
                            if (subsector != null)
                                subsectorId = subsector.SubSectorID;
                            recSubSubSector subsubsector = recSubSubSectorDal.GetBySubSubSectorCode(int.Parse(Data_Upload.SubSubSectorCode), subsectorId);
                            if (subsubsector != null)
                                subSubSectorId = subsubsector.SubSubSectorID;
                            if (debtor != null)
                                debtorId = debtor.DebtorID;
                            recTariff tariff = tariffDal.GetBySubSubSectorId((short)subSubSectorId, transType.TransTypeID);
                            if (tariff != null)
                            {
                                isFormula = tariff.IsFormula;
                                value = tariff.Value == null ? 0 : tariff.Value;
                                minvalue = tariff.MinValue == null ? 0 : tariff.MinValue;
                                maxvalue = tariff.MaxValue == null ? 0 : tariff.MaxValue;
                                percentage = tariff.Percentage == null ? 0 : tariff.Percentage;
                                tarifId = tariff.TariffID;
                                impositionBaseId = tariff.ImpositionBaseID == null ? 0 : (int)tariff.ImpositionBaseID;
                            }
                            recDebtorSector debtorsector = recDebtorSectorDal.GetById(debtorId, subSubSectorId);
                            if (debtorsector != null)
                                debtorSectorId = debtorsector.DebtorSectorID;
                            recImposition objgetImpositionId = impositionDal.GetByYearReport(Data_Upload.YearReport, debtorSectorId, subSubSectorId);
                            if (objgetImpositionId != null)
                            {
                                impositionId = objgetImpositionId.ImpositionID;
                                debtorvalue = (decimal)objgetImpositionId.DebtorValue;
                            }
                            recImposition obj = new recImposition();
                            obj.ImpositionID = impositionId;
                            obj.YearReport = short.Parse(Data_Upload.YearReport);
                            obj.DebtorSectorID = debtorSectorId;
                            obj.DebtorValue = debtorvalue;
                            if (Data_Upload.OJKValue != "")
                                obj.OJKValue = decimal.Parse(Data_Upload.OJKValue);
                            obj.ImpositionBaseID = impositionBaseId;
                            obj.Maximal = maxvalue;
                            obj.Minimal = minvalue;
                            obj.DebtorID = debtorId;
                            obj.SubSubSectorID = subSubSectorId;
                            obj.TariffValue = value;
                            obj.TariffID = tarifId;
                            obj.Percentage = percentage;
                            obj.IsFormula = isFormula;
                            obj.TransTypeID = transType.TransTypeID;
                            obj.CreatedDate = DateTime.Now;
                            obj.CreatedBy = base.UserLoggedIn;

                            recImpositionDetail objDetail = new recImpositionDetail();
                            objDetail.DebtorValue = debtorvalue;
                            if (Data_Upload.OJKValue != "")
                                objDetail.OJKValue = decimal.Parse(Data_Upload.OJKValue);
                            if (base.UserTypeID == 2)
                                objDetail.OJKValue = 0;
                            else if (base.UserTypeID == 3)
                                objDetail.DebtorValue = 0;
                            objDetail.IsActive = true;
                            objDetail.TransDate = DateTime.Now;
                            objDetail.CreateDate = DateTime.Now;
                            objDetail.CreateBy = base.UserLoggedIn;
                            collImpositions.Add(obj);
                            collImpositionDetails.Add(objDetail);
                        }
                    }
                }
                IsSuccess = impositionDal.Save(collImpositions, collImpositionDetails);

                helper.Message = "Data berhasil di simpan !";
                helper.IsSuccess = true;
            }
            catch
            {
                helper.IsSuccess = false;
                helper.Message = "Data tidak berhasil di simpan !";
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Insert";
                log.LogMessage = helper.Message;
                log.LogSource = "recImpositionUploadhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }
            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);
        }

        private void ExportToExcel(HttpContext context, DataTable table, string filename)
        {
            context.Response.Clear();
            context.Response.ClearHeaders();
            context.Response.ClearContent();

            context.Response.ContentType = "application/vnd.ms-excel";
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");

            foreach (DataColumn column in table.Columns)
            {
                context.Response.Write(column.ColumnName + "\t");
            }

            context.Response.Write(Environment.NewLine);

            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    context.Response.Write(row[i].ToString() + "\t");
                }
                context.Response.Write(Environment.NewLine);
            }

            context.Response.Flush();
            context.ApplicationInstance.CompleteRequest();

        }

        private List<recImpositionUploadform> ValidateIt(List<recImpositionUploadform> coll_Upload, Dictionary<int, Dictionary<int, string>> dictError)//, bool IsNewSession)
        {
            #region Variable
            recDebtor recDebtor = null;
            //recTransactionType transType = transTypeDal.GetByLikeTransTypeName("iuran berkala");
            recTransactionType transType = transTypeDal.GetByTransTypeCode(2);
            List<recImpositionUploadform> coll_UploadValidate = new List<recImpositionUploadform>();
            #endregion Variable

            //bool HasError = false;
            if (coll_Upload != null && coll_Upload.Count > 0)
            {
                #region Variable
                // Validating here's
                int total = coll_Upload.Count;
                string debtorCode = string.Empty;
                int sectorId = 0;
                int subSectorId = 0;
                int subSubSubSectorId = 0;
                int debtorSectorId = 0;
                string sectorCode = string.Empty;
                string subSectorCode = string.Empty;
                string subSubSectorCode = string.Empty;

                #endregion Variable
                for (int i = 0; i < coll_Upload.Count; i++)
                {
                    recImpositionUploadform imposition_Upload = coll_Upload[i];
                    //Asset_Upload.IDR = (i + 1).ToString();
                    recDebtor = debtorDal.GetByDebtorCode(imposition_Upload.DebtorCode);
                    if (recDebtor != null)
                        debtorCode = recDebtor.DebtorCode;
                    int k = i + 1;
                    if (i == 0)
                    {
                        this.ErrorMessage.AppendFormat("- Nomor Baris. {0}--> {1}", k, Environment.NewLine);
                    }
                    else
                    {
                        this.ErrorMessage.AppendFormat("{1}{1}- Nomor Baris. {0}--> {1}", k, Environment.NewLine);
                    }
                    Dictionary<int, string> dictErrorCell = new Dictionary<int, string>();
                    int counter = 0;

                    #region Kode Debitur
                    int debtorId = 0;
                    bool IsDebtorCodeError = false;

                    if (recDebtor != null)
                    {
                        if (debtorCode != imposition_Upload.DebtorCode)
                        {
                            IsDebtorCodeError = true;
                            this.ErrorMessage.AppendFormat("(Kode Debitur) - {0} tidak ada dan tidak sama dengan [{1}] {2}", debtorCode, imposition_Upload.DebtorCode, Environment.NewLine);
                        }
                        else
                        {
                            recDebtor debtor = debtorDal.GetByDebtorCode(debtorCode);
                            if (debtor != null)
                                debtorId = debtor.DebtorID;
                        }
                    }

                    if (IsDebtorCodeError)
                    {
                        HasError = IsDebtorCodeError;
                        dictErrorCell.Add(counter, "Kode Debitur");
                        counter = counter + 1;
                    }

                    #endregion Kode Debitur

                    #region Sector,SubSector,SubSubSector
                    bool IsSectorError = false;
                    bool IsSubSectorError = false;
                    bool IsSubSubSectorError = false;
                    bool IsAllSectorError = false;
                    recSector sector = new recSector();
                    recSubSector subsector = new recSubSector();
                    recSubSubSector subsubsector = new recSubSubSector();
                    if (string.IsNullOrEmpty(imposition_Upload.SectorCode))
                    {
                        IsSectorError = true;
                        ErrorMessage.AppendFormat("Kode Sektor tidak boleh kosong {0}", Environment.NewLine);
                    }
                    else
                    {
                        sector = recSectorDal.GetBySectorCode(int.Parse(imposition_Upload.SectorCode));
                        if (sector != null)
                        {
                            sectorCode = sector.SectorCode;
                            sectorId = sector.SectorID;
                        }
                    }
                    if (string.IsNullOrEmpty(imposition_Upload.SubSectorCode))
                    {
                        IsSubSectorError = true;
                        ErrorMessage.AppendFormat("Kode Sub Sektor tidak boleh kosong {0}", Environment.NewLine);
                    }
                    else
                    {
                        subsector = recSubSectorDal.GetBySubSectorCode(int.Parse(imposition_Upload.SubSectorCode), sectorId);
                        if (subsector != null)
                        {
                            subSectorId = subsector.SubSectorID;
                            subSectorCode = subsector.SubSectorCode;
                        }
                        else
                        {
                            IsSubSectorError = true;
                            ErrorMessage.AppendFormat("Kode Sub Sektor refence ke Kode Sektor  {0}", Environment.NewLine);
                        }
                    }
                    if (string.IsNullOrEmpty(imposition_Upload.SubSubSectorCode))
                    {

                        IsSubSubSectorError = true;
                        ErrorMessage.AppendFormat("Kode Sub Sub Sektor tidak boleh kosong {0}", Environment.NewLine);
                    }
                    else
                    {
                        subsubsector = recSubSubSectorDal.GetBySubSubSectorCode(int.Parse(imposition_Upload.SubSubSectorCode), subSectorId);
                        if (subsubsector != null)
                        {
                            subSubSubSectorId = subsubsector.SubSubSectorID;
                            subSubSectorCode = subsubsector.SubSubSectorCode;
                        }
                        else
                        {
                            IsSubSubSectorError = true;
                            ErrorMessage.AppendFormat("Kode Sub Sub Sektor refence ke Kode Sub Sektor {0}", Environment.NewLine);
                        }
                        recDebtorSector debtorsector = recDebtorSectorDal.GetById(debtorId, subSubSubSectorId);
                        if (debtorsector != null)
                            debtorSectorId = debtorsector.DebtorSectorID;
                        else
                        {
                            IsAllSectorError = true;
                            ErrorMessage.AppendFormat("Kode Sektor {0}, Kode Sub Sektor {1}, Kode Sub Sub Sektor {2} tidak ada di debitur {3}", imposition_Upload.SectorCode, imposition_Upload.SubSectorCode, imposition_Upload.SubSubSectorCode, Environment.NewLine);
                        }
                    }
                    if (IsSectorError)
                    {
                        HasError = IsSectorError;
                        dictErrorCell.Add(counter, "Sektor");
                        counter = counter + 1;
                    }
                    if (IsSubSectorError)
                    {
                        HasError = IsSubSectorError;
                        dictErrorCell.Add(counter, "Sub Sektor");
                        counter = counter + 1;
                    }
                    if (IsSubSubSectorError)
                    {
                        HasError = IsSubSubSectorError;
                        dictErrorCell.Add(counter, "Sub Sub Sektor");
                        counter = counter + 1;
                    }
                    if (IsAllSectorError)
                    {
                        HasError = IsAllSectorError;
                        dictErrorCell.Add(counter, "Sektor, Sub Sektor, Sub Sub Sektor");
                        counter = counter + 1;
                    }
                    #endregion Sector,SubSector,SubSubSector

                    #region Check Tarif
                    bool IsTariffError = false;
                    recTariff tariff = tariffDal.GetBySubSubSectorId(subSubSubSectorId, transType.TransTypeID);
                    if (tariff == null)
                    {
                        IsTariffError = true;
                        ErrorMessage.AppendFormat("Jenis usaha belum ada tarif nya {0}", Environment.NewLine);
                    }
                    if (IsTariffError)
                    {
                        HasError = IsTariffError;
                        dictErrorCell.Add(counter, "Cek Tarif");
                        counter = counter + 1;
                    }
                    #endregion Check Tarif
                    if (!IsDebtorCodeError && !IsSectorError && !IsSubSectorError && !IsSubSubSectorError && !IsAllSectorError && !IsTariffError)
                    {
                        this.ErrorMessage.Replace("- Nomor Baris. " + k + "-->", "");
                        this.ErrorMessage.Replace(Environment.NewLine, "");
                        imposition_Upload.IsError = "Valid";
                    }
                    else
                        imposition_Upload.IsError = "Not Valid";

                    #region Collecting Error
                    if (dictErrorCell.Count > 0)
                    {
                        dictError.Add(i, dictErrorCell);
                    }
                    #endregion Collecting Error
                    coll_UploadValidate.Add(imposition_Upload);
                    coll_Upload[i] = imposition_Upload;
                }
            }
            return coll_UploadValidate;
        }
    }
    public class recImpositionUploadform
    {
        public string DebtorCode { get; set; }
        public string DebtorName { get; set; }
        public string SectorCode { get; set; }
        public string SectorName { get; set; }
        public string SubSectorCode { get; set; }
        public string SubSectorName { get; set; }
        public string SubSubSectorCode { get; set; }
        public string SubSubSectorName { get; set; }
        public string YearReport { get; set; }
        public string OJKValue { get; set; }
        public string DebtorValue { get; set; }
        public string IsError { get; set; }

    }
}